"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.
"""

"""
Case Type   : 资源池化
Case Name   : 资源池化场景下,failover前后执行全局临时表相关操作
Create At   : 2025-03-03
Owner       : zhongchengzhi
Description :
    1、查询集群状态
    2、创建全局临时表
    3、增加列
    4、查询表
    5、删除新增加的列
    6、构造主机dn故障
    7、查询表
    8、插入数据
    9、查询表
    10、清理环境
    11、恢复集群原始状态

Expect      :
    1、查询结果正常
    2、创建全局临时表成功
    3、增加列成功
    4、查询表成功
    5、删除新增加的列成功
    6、主机failover成功
    7、查询表成功
    8、插入数据成功
    9、查询表成功
    10、清理成功
    11、恢复集群原始状态成功,集群状态正常
History     :
    1、2025-03-03 创建
    2、2025-07-12 改造用例为1p1s环境可执行
"""
import os
import unittest
import time

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro

Primary_ROOTSH = CommonSH('PrimaryRoot')
Primary_SH = CommonSH('PrimaryDbUser')
Standby1_SH = CommonSH('Standby1DbUser')

@unittest.skipIf(1 == Primary_SH.get_node_num(), '单机环境不执行')
class SharedStorage(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.constant = Constant()
        self.s1_comsh = CommonSH('Standby1DbUser')

    def test_shared_storage(self):
        text = '-----step1:查询数据库集群状态-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)

        text = '-----step2:创建全局临时表;expect:成功-----'
        self.log.info(text)
        sql = f'''
                drop table if exists temp_table_alter_010; 
                create global temporary table temp_table_alter_010(
                    c_id int,
                    c_integer integer,
                    c_real real,
                    c_float float,
                    c_double binary_double,
                    c_decimal decimal(38),
                    c_number number(38),
                    c_number1 number,
                    c_number2 number(20,10),
                    c_numeric numeric(38),
                    c_char char(50) default null,
                    c_varchar varchar(20),
                    c_varchar2 varchar2(4000),
                    c_clob clob,c_raw raw(20),
                    c_blob blob,c_date date,
                    c_timestamp timestamp)on commit preserve rows;
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('CREATE TABLE', status, '执行失败' + text)

        text = '-----step3:增加列;expect:成功-----'
        self.log.info(text)
        sql = f'''
                alter table temp_table_alter_010 add(c_1 int);
                alter table temp_table_alter_010 add(c_2 BLOB);
                alter table temp_table_alter_010 add(c_3 varchar(200));
                alter table temp_table_alter_010 add(c_4 clob);
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('ALTER TABLE\nALTER TABLE\nALTER TABLE\nALTER TABLE', status, '执行失败' + text)

        text = '-----step4:查询表结构;expect:成功-----'
        self.log.info(text)
        sql = f"select count(*) from temp_table_alter_010;"
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('(1 row)', status, '执行失败' + text)

        text = '-----step5:删除新增加的列;expect:成功-----'
        self.log.info(text)
        sql = f'''
                alter table temp_table_alter_010 drop c_1;
                alter table temp_table_alter_010 drop c_2;
                alter table temp_table_alter_010 drop c_3;
                '''
        self.log.info(sql)
        status = Primary_SH.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('ALTER TABLE\nALTER TABLE\nALTER TABLE', status, '执行失败' + text)

        text = '-----step6:构造主机DN故障 expect:集群failover成功-----'
        self.log.info(text)
        stopdn = Primary_SH.exec_failover()
        self.log.info(stopdn)
        time.sleep(40)
        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        res1 = status.splitlines()[-1].split('|')[0].split('6001')[-1]
        res2 = status.splitlines()[-1].split('|')[1].split('6002')[-1]
        self.log.info(res1)
        self.log.info(res2)
        self.assertIn('P Down    Manually stopped', res1, '执行失败' + text)
        self.assertTrue('S Primary Normal' in res2, '执行失败' + text)

        text = '-----step7:查询表数据;expect:成功-----'
        self.log.info(text)
        sql = f"select * from temp_table_alter_010;"
        self.log.info(sql)
        status = self.s1_comsh.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('(0 rows)', status, '执行失败' + text)

        text = '-----step8:向表中插入数据;expect:成功-----'
        self.log.info(text)
        sql = f'''
                insert into temp_table_alter_010 values(1,0,3.14,1.0002,3.55555,5,7887.656,0,0.111111,3.1415926,
                'dghg','jjj','pokj99','ywgdghghhgghe',hextoraw('101a1101'),'010101101',date_trunc(
                'hour', timestamp  '2001-02-16 20:38:40'),to_timestamp('2019-01-03 14:58:54.000000',
                'yyyy-mm-dd hh24:mi:ss.ffffff'),'汉字的环境ihhivhkhv&*&%^&^555');
                insert into temp_table_alter_010 select * from temp_table_alter_010;
                insert into temp_table_alter_010 select * from temp_table_alter_010;
                insert into temp_table_alter_010 select * from temp_table_alter_010;
                analyze temp_table_alter_010;
                '''
        self.log.info(sql)
        status = self.s1_comsh.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('INSERT 0 1\nINSERT 0 1\nINSERT 0 2\nINSERT 0 4\nANALYZE', status, '执行失败' + text)

        text = '-----step9:查询表结构;expect:成功-----'
        self.log.info(text)
        sql = f"select count(*) from temp_table_alter_010;"
        self.log.info(sql)
        status = self.s1_comsh.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('(1 row)', status, '执行失败' + text)

        text = '-----step10:清理环境;expect:成功-----'
        self.log.info(text)
        sql = f"drop table temp_table_alter_010;"
        self.log.info(sql)
        status = self.s1_comsh.execut_db_sql(sql)
        self.log.info(status)
        self.assertIn('DROP TABLE', status, '执行失败' + text)

    def tearDown(self):
        text = '-----step11:恢复环境为初始集群状态; expect:成功-----'
        self.log.info(text)
        status = Primary_SH.exec_cm_ctl(mode='stop')
        self.log.info(status)
        self.assertIn('cm_ctl: stop cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='start')
        self.log.info(status)
        self.assertIn('cm_ctl: start cluster successfully.', status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='switchover', param='-a')
        self.log.info(status)
        self.assertIn(self.constant.cm_switchover_success_msg, status, '执行失败' + text)

        status = Primary_SH.exec_cm_ctl(mode='query', param='-Cvipd')
        self.log.info(status)
        self.assertIn('cluster_state   : Normal', status, '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
